﻿using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System;
using System.Data;
using System.ServiceProcess;

namespace CitiSurface.Model
{
    class DataUtilities
    {
        public static Interviewee getInterviewee(int tagValue, SqlConnection myCon)
        {
           //define 
            string firstName = "";
            string lastName = "";
            ObservableCollection<Interview> interviewList = new ObservableCollection<Interview>();
            string strSQL = @"select interviewee.TagValue, Interviewee.FirstName as IntervieweeFN, Interviewee.LastName as intervieweeLN, Interviewer.FirstName,Interviewer.LastName,School,Department,WorkEmail,CellPhone,Interest,HomePhone,pictureURL,AboutMe,StartTime,EndTime,Memo
                              from Interviewer,Interviewee,Interview
                             where Interview.InterviewerID = Interviewer.InterviewerID
                             and Interview.IntervieweeID = Interviewee.IntervieweeID
                              and interviewee.TagValue = @tagValue";
            //SQL command
            SqlCommand MyCommand = new SqlCommand(strSQL, myCon);
            //add parameter
            SqlParameter para = new SqlParameter("@tagValue", SqlDbType.Int, 4);
            para.Value = tagValue;
            MyCommand.Parameters.Add(para);
            //execute SQL
            SqlDataReader MyDataReader = MyCommand.ExecuteReader();
            //read data
            while (MyDataReader.Read())
            {
                firstName = MyDataReader["IntervieweeFN"].ToString();
                lastName = MyDataReader["intervieweeLN"].ToString();
                interviewList.Add(new Interview(new Interviewer(MyDataReader["FirstName"].ToString(), MyDataReader["LastName"].ToString(), MyDataReader["School"].ToString(), MyDataReader["Department"].ToString(), MyDataReader["WorkEmail"].ToString(), MyDataReader["CellPhone"].ToString(), MyDataReader["Interest"].ToString(), MyDataReader["HomePhone"].ToString(), MyDataReader["pictureURL"].ToString(), MyDataReader["AboutMe"].ToString()),Convert.ToDateTime(MyDataReader["StartTime"]), Convert.ToDateTime(MyDataReader["EndTime"]), MyDataReader["Memo"].ToString()));
            }
            //return
            return new Interviewee(firstName, lastName, interviewList);
        }

        public static bool existSqlServerService()
        {
            bool ExistFlag = false;

            ServiceController[] service = ServiceController.GetServices();
            for (int i = 0; i < service.Length; i++)
            {
               // Console.WriteLine(service[i].DisplayName.ToString());
                if (service[i].DisplayName.ToString() == "SQL Server (MSSQLSERVER)")
                {
                    ExistFlag = true;
                }
            }
            return ExistFlag;
        }
    }
}
